home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Fritz: All Fritz
/
All Fritz.zip
/
All Fritz
/
FILES
/
ACCOUTIL
/
LTSTIPS3.LZH
/
LOTUSINV
< prev
next >
Wrap
Text File
|
1985-11-28
|
7KB
|
116 lines
A 1-2-3 Macro for Investors
(PC Magazine Vol 3 No 20 Oct 16, 1984 by G. Hayles)
This article explains how to create and use an internal rate of
return (IRR) macro, or subroutine, that is easily incorporated into
any 123 model. The file LOTUSINV contains the IRR macro and describes
what each line of the macro does. The letter A (for Analysis) is
assigned to the macro and it is invoked with the Alt-A key combination.
The macro is stored in cells A2001 through A2011 and the descriptions
in cells B2001 through B2011.
You must use certain conventions for telling 123 how to move the
cursor. For instance, including the command {GOTO} in a macro has the
same effect as striking the F5 function key, and using the command {UP}
{UP} moves the cursor two rows up. (Refer to page 109 in the 123 user
manual for more details on the conventions used for cursor movement and
other macro-invoked commands.)
Once the keystrokes are loaded, a macro range name consisting of a
reverse slash and a single letter of the alphabet is assigned to the
cell at which you start the macro. You can then invoke the macro any
time you need it by pressing the letter of the alphabet assigned to the
macro while holding down the Alt key.
You can put this feature to many practical uses. For example,
suppose you are evaluating the pros and cons of investing in two tax
shelters. Each investment requires a down payment and cash "inflows"
and "outflows" -- additional payments and returns -- of varying amounts
and at various times. With inflation, a dollar today is worth more than
a dollar tomorrow, so you will compute the internal rate of return of
each investment to take this "time value of money" into consideration
when you compare the relative worth of each investment. Your decision
to accept or reject each investment will be based on a minimum
requirement of 15 percent return on investment.
Investment A requires a down payment of $200. It also requires
you to put in $30 and $20 at the end of the first and second years,
respectively. You receive cash from the investment in years 3 through
10, $100 in year 3, $50 in years 4 and 5, $70 in year 6, $90 in year 7,
$100 in year 8, $60 in year 9, and $30 in year 10.
Investment B requires a down payment of $100. Cash must also be
paid in at the end of years 1 through 3 in the amounts of $10, $30, and
$10, respectively. You receive a grand total of $20 cash from this
investment in all 10 years of the investment's life. A table reflecting
the investments individually and collectively is also contained in the
file LOTUSINV.
You can easily evaluate the investment by using the IRR macro.
You enter the down payment and cash inflows and outflows for each
investment and for the two investments combined, and the reports
illustrating the return on investment for Investment A, Investment B,
and Investment A and B together are generated. To produce these
reports, first you retrieve the IRR subroutine with the macro and
description loaded in the area beginning with cell A2001. Use rows 1
through 99 as a "worksheet" area to create output from the macro and
then use 123's MOVE command to move the results created in this
worksheet to other parts of the spreadsheet. After the results are
moved, assign a name to them using the RANGE NAME command. You use
this name for printing the results of the analysis when called for.
Next, create cells for the investment cash inflows and outflows in
some part of the spreadsheet other than the worksheet area. Note the
amount of the down payment for each investment and the location of the
rows containing cash inflows and outflows for each investment.
Finally, as you evaluate each investment using the IRR macro enter
this down payment and row location information. The results of each
investment will be output in the worksheet area. As 123 produces the
report for each investment alternative, move and name the report. The
internal rate of return report can then be printed using the range name
assigned to it.
As the results show, you would reject Investment B and accept
Investment A using the 15 percent return on investment criterion.
The third return on investment analysis shows that you can analyze any
number of combined investments using the IRR macro. You do it by giving
the combined cash inflows and outflows as input to the macro instead of
individual cash flows for each investment. This IRR macro can be used
repeatedly for any 123 spreadsheet that requires an internal reate of
return calculation. The only information you need is the amount of the
down payment, if any, and the starting location of the rows for cash
outflows and cash inflows.
-----------------------------------------------------------------
Maintaining a Securities List
(PC Magazine Vol 4 No 26 Dec 24, 1985 Spreadsheet Clinic)
We use 123 to keep track of investments, and often need to print
a list of only those which have yet to mature. We use one row of a
spreadsheet per investment, with maturity dates shown in column A.
This macro sends the cursor to the beginning of the maturity date
column and then tests each date to see whether it was earlier than a
certain date, in this case, today's date. The macro will then delete
every investment that matured before today. When it reaches a maturity
date of today or later, it prints a report of predefined dimensions
called RANGE.
\X {goto}A5~ Goto date column
TEST /xiA5<@today~/xgDEL~ Test date, goto DEL
/pprRANGE~gq Print RANGE
DEL /wdr~/xgTEST~ Delete row, goto TEST
Editor's Note: Two things are troublesome with this macro. The
first is that it stops working as soon as it runs into a maturity date
that is today or later. This means that all your securities have to be
in proper date order. In case your securities get rearranged, or if
you'd rather order then in some other way, this macro looks through the
entire column of dates and deletes only those investments that have
already matured. This way the dates don't have to be in order.
\Y {goto}A5~ Goto date column
START /rncCURCELL~{bs}~ Name current cell CURCELL
/xiCURCELL=0~/xq Quit if CURCELL is blank
/xiCURCELL<@today~/xgDELETE~ Test CURCELL, goto DELETE
{down}/xgSTART~ Down one row, goto START
DELETE /wdr~/xgSTART~ Delete row, goto START
The other things concerns the print range. If you are deleting an
unknown number of rows from the worksheet, how do you know what your
final print range is? You might want to include a print macro that
would figure out how large your spreadsheet is before printing it.
Such a routine was included in the Spreadsheet Column Vol 4 No 17
August 20, 1985 "Printing a Worksheet of Unknown Size."